﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Management;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Utility;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;
using Microsoft.SqlServer.Management.Smo.Wmi;
using Microsoft.SqlServer.Server;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security;
using System.Security.Permissions;
using DocManagerORM.ORM;

namespace DocManagerORM.Utils
{
   public class SqlServer
    {
        //SYS_SPROC_VERSION  > sp_helpserver '.\SQLEXPRESS'

        public System.Data.SqlClient.SqlConnection  Connessione { get; set; }
        public String ConnString { get; set; }

       System.Data.Sql.SqlDataSourceEnumerator m_Enumeratore = null;

       public SqlServer()
       {
          //m_Enumeratore= System.Data.Sql.SqlDataSourceEnumerator.Instance ;
       }

       public SqlServer(string pConnString, bool pUsaAppConfigName)
       {
           if (pUsaAppConfigName)
               ConnString =System.Configuration.ConfigurationManager.ConnectionStrings[pConnString].ConnectionString;
           else
                ConnString = pConnString;

           if (string.IsNullOrEmpty(ConnString))
               return;

           OpenConnection(true);
       }


       public static DataTable enumeraIstanzeSqlServer()
       {
           var enumerator = System.Data.Sql.SqlDataSourceEnumerator.Instance;
           var dt = enumerator.GetDataSources();
           enumerator = null;

           var coll = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(true);

           var name = coll.Rows[0]["server"].ToString();

           //var ret = Win32WMI_Service.enumeraIstanzeServiziViaWMI(name, "Sql Server");//SQL Server (SQLEXPRESS)

           return dt;
  
       }

       private static object Win32WMI_ServiceenumeraIstanzeServiziViaWMI(string name, string p)
       {
           throw new NotImplementedException();
       }//fM


       private void buildServerMap()
       {
           //var sqlServersMap = new Dictionary<string, string>();

           ////get all available SQL Servers     
           //Application sqlApp = new SQLDMO.ApplicationClass();
           //SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();

           //ArrayList servs = new ArrayList();

           //for (int i = 0; i < sqlServers.Count; i++)
           //{
           //    object srv = sqlServers.Item(i + 1);

           //    if (srv != null)
           //    {
           //        sqlServersMap.Add(srv.ToString(), srv.ToString());
           //    }
           //}
       }


       public DbDataReader execSQL(string pSQL)
       {
           if(!OpenConnection(true)) return null;
           using (var com = getCommand(pSQL, CommandType.Text))
           {
               return com.ExecuteReader();
           }

       }

       public static string[] enumeraIstanzeServiziViaWMI(string hostName, string pNameServiceContain)
       {
           var sb = new StringBuilder();
           string[] ret = null;

           pNameServiceContain = pNameServiceContain.ToLower(); 
 
           try
           {
               var path = @"\\" + hostName + @"\root\cimv2";

               ManagementScope ms = new ManagementScope(path);
               ms.Connect();
               //var sqlServerServiceName = "sqlservr";


               using (ManagementObjectSearcher searcher = new ManagementObjectSearcher(
                   "SELECT * FROM Win32_Service WHERE Started = TRUE "))
               {
                   //AND DisplayName='" + sqlServerServiceName + "'"

                   searcher.Scope = ms;

                   string servizioName = "";

                   if (searcher != null && searcher.Get() != null)
                   {
                       foreach (ManagementObject service in searcher.Get())
                       {
                           servizioName = service["Caption"].ToString().ToLower() ;

                           if (servizioName.Contains(pNameServiceContain))
                               sb.Append(hostName + ". " + servizioName + " ( RUNNING \r\n");
                       }
                   }
               }

               ret = sb.ToString().Split(new string[]{"\r\n"}, StringSplitOptions.RemoveEmptyEntries)  ;
               sb.Clear();

               return ret;

           }
           catch (Exception e)
           {
               sb.Append(hostName + " service - UNVERIFIABLE\n");
               
           }

           return ret;
       
       }

       public bool OpenConnection(bool pMessaggia= false )
       {
           if (Connessione == null)
               Connessione = new SqlConnection(ConnString);

           try
           {
               if(Connessione.State ==ConnectionState.Closed )Connessione.Open();  

               return true;
           }
           catch (Exception ex)
           {
               if (pMessaggia)
                   System.Windows.MessageBox.Show("Impossibile aprire la connessione :\r\n"+ ex.Message); 
               return false;           
           }

       }//fM

       public DbCommand getCommand(string pSql, CommandType pComType)
       {
           if (!OpenConnection(true)) return null;
           var com =Connessione.CreateCommand();
           com.CommandText = pSql;
           com.CommandType = pComType;
           return com;
       }//fM

       public  void PopolaClassiPOCOdaDB(string pPathDirectoryToSave)
       {

           var allObj = QueryBase.getAllTablesAndViewsInModel(true).ToList();

           try
           {
               using (var dr = getCommand("Select top 1 * from categorie", CommandType.Text).ExecuteReader())
               {
                   if (dr == null || dr.IsClosed)
                       allObj = null;

                   var gen = Utils.DynamicBuilder<ORM.Categorie>.CreateBuilder(dr);

                   

                   var dr2 = getCommand("Select * from categorie", CommandType.Text).ExecuteReader();
                   
                   var dati = gen.BuildAll(dr2);

                   dr2.Close();dr2.Dispose();  
               }
           }
           catch (Exception ex)
           {
               var msg = ex.Message;
           }
       }//fM


       public string getNameSpacesUsed(params string[] pOtherNamespaces)
       {
          string ret =  "using System;\r\n" +
           "using System.Collections.Generic;\r\n" +
           "using System.ComponentModel;\r\n" +
           "using System.Linq;\r\n" +
           "using System.Text;\r\n";

          if (pOtherNamespaces != null)
          {
              foreach (var item in pOtherNamespaces)
                ret += "using " + item +" ;\r\n";                           
          }


          return (ret+"\r\n" );

       }//fM


       public void GeneraClassiPocodaDb(string pNamespace, bool pChiaviEsterneCollections, string pPathDirectory = "DbGeneratorClasses")
       {
           //System.Data.DataTable tbl = Connessione.GetSchema("MetadataCollections");
           bool usaODX = false;
           StringBuilder sb = new StringBuilder(1000);

           string[] restrictions = new string[4];
           restrictions[1] = "dbo";

           string intestazione = "// Generated by DS2171 tool - DateTime : " +
               DateTime.Now.ToShortDateString() +"  "+ DateTime.Now.ToLongTimeString() + "\r\n";  

           //Connessione.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables,
           //               new string[] { null, "dbo", null, "BASE TABLE" });

           
           for (int i = 0; i < 2; i++)
           {
               usaODX = i == 0 ? false : true;
               if (usaODX) pPathDirectory += "_ODX";

               if (!Directory.Exists(pPathDirectory)) Directory.CreateDirectory(pPathDirectory);

               string tbName = ""; 
               using (DataTable table = Connessione.GetSchema("Tables", restrictions))
               {
                   foreach (System.Data.DataRow row in table.Rows)
                   {
                       tbName = row["Table_name"].ToString();

                       sb.Clear();
                       sb.AppendLine(intestazione);

                       if(usaODX)
                            sb.Append(getNameSpacesUsed("ODX.Core"));
                       else
                           sb.Append(getNameSpacesUsed());

                       sb.AppendFormat("namespace {0} ", pNamespace).AppendLine();
                       sb.AppendLine(" { \r\n");

                       sb.AppendLine("[Serializable]");
                       sb.AppendLine("[Table]");
                       sb.AppendFormat("public class {0} {1}", tbName, (usaODX? " : ODX.Core.Entity":"" ));
                       sb.AppendLine("\r\n { \r\n");

                       sb.AppendFormat("public {0} () // <ctor>",tbName ); //costruttore
                       sb.AppendLine("\r\n {").AppendLine(" ").AppendLine("} \r\n");

                       sb.Append(getSpecificheClasse(tbName, pChiaviEsterneCollections, usaODX).ToString());
                  
                       sb.AppendLine("}//fC ").AppendLine();
                       sb.AppendLine(" }//fN ");

                       using (var fil = File.CreateText(Path.Combine(pPathDirectory, tbName + ".cs")))
                       {
                           fil.AutoFlush = true; fil.Write(sb.ToString());
                           fil.Close();
                       }
                   }
               }
           } 
       
       }//fM

       public StringBuilder getSpecificheClasse(string pTableName, bool pChiaviEsterneCollections,bool pUsaODXLayer)
       {
            StringBuilder sb = new StringBuilder(500);
            string[] restrictions = new string[] { null, null, pTableName };
            string colName = string.Empty;
            SQlConstraints vincolo = null;

           try
           {
               using (var dr = getCommand("Select top 1 * from " + pTableName, CommandType.Text).ExecuteReader())
               {
                   if (dr == null || dr.IsClosed) return sb;
                   using (System.Data.DataTable table = dr.GetSchemaTable())
                   {
                       var Constraints = getConstraintsdefinitionsTable(pTableName);

                           foreach (System.Data.DataRow row in table.Rows)
                           {
                               colName = row["ColumnName"].ToString();
                               vincolo = Constraints.Where(x => x.InColumn == colName).FirstOrDefault();

                               if (vincolo != null)
                               {
                                   if (vincolo.Type.ToLower().Contains("default"))
                                   { 
                                   }
                                   else if (vincolo.Type.ToLower().Contains("primary"))
                                   {
                                       sb.AppendFormat("//ConsDef: {0} ", vincolo.Type).Append("\r\n");
                                       sb.AppendFormat(" public {0} {1} ", row["DataType"].ToString(), colName);
                                   }
                                   else if (vincolo.Type.ToLower().Contains("foreign"))
                                   {
                                       sb.AppendFormat("//ConsDef: {0} ", vincolo.Definition).Append("\r\n");
                                       if (pChiaviEsterneCollections)
                                       {
                                           if (pUsaODXLayer)
                                               sb.AppendFormat(" public IEntityList<{0}> {1} ", vincolo.RelatedTable, colName);
                                           else
                                            sb.AppendFormat(" public IList<{0}> {1} ", vincolo.RelatedTable, colName);
                                       }

                                       else
                                           sb.AppendFormat(" public {0} {1} ", row["DataType"].ToString(), colName);
                                   }                                  
                               }
                               else
                               {
                                   sb.AppendFormat("//SQlType: {0} - Nullable?: {1} - Unique?: {2} - Identity?: {3}",
                                       row["Datatypename"], row["AllowDbNull"], row["IsUnique"], row["IsIdentity"]).Append("\r\n");

                                   sb.AppendFormat(" public {0} {1} ", row["DataType"].ToString(), colName);

                               }

                               sb.Append("{");
                               sb.AppendFormat(" get; {0} ", row["IsReadOnly"].ToString() == "False" ? "set;" : "set;");
                               sb.Append("}").AppendLine();

                               sb.AppendLine(" ");
                           }                      
                   }
               }
               return sb;
           }
           catch (Exception ex)
           {
               throw;
           }
       
       }//fm

       //per sql server 2005/2008
       public IList<SQlConstraints> getConstraintsdefinitionsTable(string pTableName)
       {
           string sql = "exec sys.sp_helpconstraint '" + pTableName  + "'";
           IList<SQlConstraints> listRet = new List<SQlConstraints>();
           SQlConstraints cons =null;

           using (var dr = getCommand(sql, CommandType.Text).ExecuteReader())
           {
               if (dr == null || dr.IsClosed) return listRet;

               dr.NextResult();

               while (dr.Read())
               {
                   if (dr.GetString(0).ToLower().Contains("key"))
                   {
                       cons = new SQlConstraints()
                       {
                           Type = dr.GetString(0),//tipo 
                           Name = dr.GetString(1),//nome 
                           InColumn = dr["constraint_keys"].ToString(),
                           Definition = dr["constraint_keys"].ToString()
                       };

                       if (dr.Read())
                       {
                           if (dr.GetString(0).ToLower().Contains("key"))
                               throw new Exception("Errore nella valutazione dei constraints !!");
                           cons.Definition = dr["constraint_keys"].ToString();
                       }


                       if (!string.IsNullOrEmpty(cons.Definition))
                       {
                           if (cons.Definition.ToLower().Contains("references"))
                           { 
                                cons.RelatedTable = cons.Definition.Split(' ')[1].Split('.').Last();   
                           }
                       }

                   }
                   else if (dr.GetString(0).ToLower().Contains("default"))
                   {                      
                           cons = new SQlConstraints()
                           {
                               Type = dr.GetString(0),//tipo 
                               Name = dr.GetString(1),//nome 
                               InColumn = dr["constraint_keys"].ToString(),
                               Definition = dr["constraint_keys"].ToString()
                           };                                           
                   }

                   listRet.Add(cons);
               }
           }
           return listRet;
       }//fN

       public  DataTable GetSchema(string collectionName, string[]restrictionValues)
       {          
           return Connessione.GetSchema(collectionName, restrictionValues);
       }

    }//fC


   public class SQlConstraints {

       public string Type { get; set; }
       public string Name { get; set; }
       public string InColumn { get; set; }
       public string Definition { get; set; }
       public string RelatedTable { get; set; }
       public string[] RelatedFields { get; set; }
   
   }//fC

}
